<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.text.DateFormat"%>
<%@page import="util.UserMethod"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="factory.OracleDAOFactory"%>
<%@ page import = "java.sql.*" %>
  <%
      // set content type and xml tag
      response.setContentType("text/xml");
      out.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
 
      // define variables from incoming values
      String posStart = "";
      if (request.getParameter("posStart") != null){
          posStart = request.getParameter("posStart");
      }else{
          posStart = "0";
      }   
 
      String count = "";
      if (request.getParameter("count") != null){
          count = request.getParameter("count");
      }else{
          count = "100";   
      }   
 
      // connect to database
      	Connection conn = null;		
		Statement  stmt = null;
		ResultSet rs = null;
		conn = OracleDAOFactory.createConnection();
		
      // query to products table
      String sql = "SELECT * FROM education e, employee_info ei WHERE e.employee_id = ei.employee_id";
 
      // if this is the first query - get total number of records in the query result
      String totalCount = "";
      if (posStart.equals("0")){
          String sqlCount = "Select count(*) as cnt from education";
          stmt = conn.createStatement();
          rs = stmt.executeQuery(sqlCount);
          rs.next();
          totalCount = rs.getString("cnt");
          rs.close();
      } else {
          totalCount = "";
      }
 
      // add limits to query to get only rows necessary for output
      sql += " AND rownum BETWEEN " + posStart + " and " + count;
 
      // Execute the query
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
 
      // output data in XML format  
      out.println("<rows>");
      out.println("<head>");
      out.println("<column width='60' type='ed' align='center' sort='str'>사번</column>");
      out.println("<column width='60' type='ed' align='center' sort='str'>직급</column>");
      out.println("<column width='60' type='ed' align='center' sort='str'>이름</column>");
      out.println("<column width='100' type='ed' align='center' sort='str'>구분</column>");
      out.println("<column width='*' type='ed' align='center' sort='str'>학교명</column>");
      out.println("<column width='150' type='ed' align='center' sort='str'>입학날짜</column>");
      out.println("<column width='150' type='ed' align='center' sort='str'>졸업날짜</column>");
      out.println("<column width='100' type='ed' align='center' sort='str'>전공</column>");
      out.println("<column width='100' type='ed' align='center' sort='str'>최종학점</column>");
      out.println("</head>");
      request.setCharacterEncoding("UTF-8");
      DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
      UserMethod um = new UserMethod(); 
      while (rs.next()) {
          out.println("<row id='" + rs.getString("education_id") + "'>");
              out.println("<cell>");
                  out.println(rs.getString("employee_id")); 
              out.println("</cell>");
              out.println("<cell>");
                  out.println(rs.getString("position"));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("employee_kname")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("school_type")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("school_name")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(df.format(rs.getDate("start_date")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(df.format(rs.getDate("end_date")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("major_code")));
              out.println("</cell>");
              out.println("<cell>");
                  out.println(um.replaceNull(rs.getString("final_score")));
              out.println("</cell>");
          out.println("</row>");
      }
      out.println("</rows>");
      rs.close();
      conn.close();
      stmt.close();
  %>
  
  